﻿
CREATE PROCEDURE [dbo].[proc_Finance_AccountsRecivableAnalysis]
AS
BEGIN

	declare @CurrentDate datetime=dateadd(MONTH,-1,Getdate())
	declare @OneMonth varchar(10),@ThreeMonth varchar(10),@SixMonth varchar(10),@OneYear varchar(10),@TwoYear varchar(10)
	Set @OneMonth=CONVERT(varchar(10),dateadd(MONTH,-1,@CurrentDate),120)
	Set @ThreeMonth=CONVERT(varchar(10),dateadd(MONTH,-4,@CurrentDate),120)
	Set @SixMonth=CONVERT(varchar(10),dateadd(MONTH,-7,@CurrentDate),120)
	Set @OneYear=CONVERT(varchar(10),dateadd(MONTH,-13,@CurrentDate),120)
	Set @TwoYear=CONVERT(varchar(10),dateadd(MONTH,-25,@CurrentDate),120)

	Select 1 N,z2.id mid,z2.name,Sum(OneMonth) OneMonth,Sum(OneToThreeMonth) OneToThreeMonth,Sum(ThreeToSixMonth) ThreeToSixMonth,
		Sum(SixMonthToOneYear) SixMonthToOneYear,Sum(OneYearToTwoYear) OneYearToTwoYear,Sum(TwoYearAbove) TwoYearAbove,Sum(Total) Total
	 from (Select x.CompanyId,
			Case When AuditDate>@OneMonth then daishou else 0 end OneMonth,
			Case When AuditDate>@ThreeMonth and AuditDate<=@OneMonth then daishou else 0 end OneToThreeMonth,
			Case When AuditDate>@SixMonth and AuditDate<=@ThreeMonth then daishou else 0 end ThreeToSixMonth,
			Case When AuditDate>@OneYear and AuditDate<=@SixMonth then daishou else 0 end SixMonthToOneYear,
			Case When AuditDate>@TwoYear and AuditDate<=@OneYear then daishou else 0 end OneYearToTwoYear,
			Case When AuditDate<=@TwoYear then daishou else 0 end TwoYearAbove,daishou Total
			from (Select CompanyId,YingShouPrice-ShiShouPrice daishou,CONVERT(varchar(10),AuditDate,120) AuditDate from [Order] 
					Where YingShouPrice-ShiShouPrice>0 And StateId>=1 And NOE_Flag<>2 And FinanceRenlingDate is not null) x) y 
					join Zzjg z1 on y.CompanyId=z1.CompanyId join Zzjg z2 on z1.pid=z2.id
	 where z2.id in (12,13,14)
	Group by z2.id,z2.name
	union
	Select 2,0,'小计',Sum(OneMonth) OneMonth,Sum(OneToThreeMonth) OneToThreeMonth,Sum(ThreeToSixMonth) ThreeToSixMonth,
		Sum(SixMonthToOneYear) SixMonthToOneYear,Sum(OneYearToTwoYear) OneYearToTwoYear,Sum(TwoYearAbove) TwoYearAbove,Sum(Total) Total
	 from (Select x.CompanyId,
			Case When AuditDate>@OneMonth and AuditDate<=@CurrentDate then daishou else 0 end OneMonth,
			Case When AuditDate>@ThreeMonth and AuditDate<=@OneMonth then daishou else 0 end OneToThreeMonth,
			Case When AuditDate>@SixMonth and AuditDate<=@ThreeMonth then daishou else 0 end ThreeToSixMonth,
			Case When AuditDate>@OneYear and AuditDate<=@SixMonth then daishou else 0 end SixMonthToOneYear,
			Case When AuditDate>@TwoYear and AuditDate<=@OneYear then daishou else 0 end OneYearToTwoYear,
			Case When AuditDate<=@TwoYear then daishou else 0 end TwoYearAbove,daishou Total
			from (Select CompanyId,YingShouPrice-ShiShouPrice daishou,CONVERT(varchar(10),AuditDate,120) AuditDate from [Order] 
					Where YingShouPrice-ShiShouPrice>0 And StateId>=1 And NOE_Flag<>2 And FinanceRenlingDate is not null) x) y 
					join Zzjg z1 on y.CompanyId=z1.CompanyId join Zzjg z2 on z1.pid=z2.id
	 where z2.id in (12,13,14)
	 union
	 Select 3,z2.id mid,z2.name,Sum(OneMonth) OneMonth,Sum(OneToThreeMonth) OneToThreeMonth,Sum(ThreeToSixMonth) ThreeToSixMonth,
		Sum(SixMonthToOneYear) SixMonthToOneYear,Sum(OneYearToTwoYear) OneYearToTwoYear,Sum(TwoYearAbove) TwoYearAbove,Sum(Total) Total
	 from (Select x.CompanyId,
			Case When AuditDate>@OneMonth and AuditDate<=@CurrentDate then daishou else 0 end OneMonth,
			Case When AuditDate>@ThreeMonth and AuditDate<=@OneMonth then daishou else 0 end OneToThreeMonth,
			Case When AuditDate>@SixMonth and AuditDate<=@ThreeMonth then daishou else 0 end ThreeToSixMonth,
			Case When AuditDate>@OneYear and AuditDate<=@SixMonth then daishou else 0 end SixMonthToOneYear,
			Case When AuditDate>@TwoYear and AuditDate<=@OneYear then daishou else 0 end OneYearToTwoYear,
			Case When AuditDate<=@TwoYear then daishou else 0 end TwoYearAbove,daishou Total
			from (Select CompanyId,YingShouPrice-ShiShouPrice daishou,CONVERT(varchar(10),AuditDate,120) AuditDate from [Order] 
					Where YingShouPrice-ShiShouPrice>0 And StateId>=1 And NOE_Flag<>2 And FinanceRenlingDate is not null) x) y 
					join Zzjg z1 on y.CompanyId=z1.CompanyId join Zzjg z2 on z1.pid=z2.id
	 where z2.id in (281,282)
	Group by z2.id,z2.name
	union
	Select 4,0,'小计',Sum(OneMonth) OneMonth,Sum(OneToThreeMonth) OneToThreeMonth,Sum(ThreeToSixMonth) ThreeToSixMonth,
		Sum(SixMonthToOneYear) SixMonthToOneYear,Sum(OneYearToTwoYear) OneYearToTwoYear,Sum(TwoYearAbove) TwoYearAbove,Sum(Total) Total
	 from (Select x.CompanyId,
			Case When AuditDate>@OneMonth and AuditDate<=@CurrentDate then daishou else 0 end OneMonth,
			Case When AuditDate>@ThreeMonth and AuditDate<=@OneMonth then daishou else 0 end OneToThreeMonth,
			Case When AuditDate>@SixMonth and AuditDate<=@ThreeMonth then daishou else 0 end ThreeToSixMonth,
			Case When AuditDate>@OneYear and AuditDate<=@SixMonth then daishou else 0 end SixMonthToOneYear,
			Case When AuditDate>@TwoYear and AuditDate<=@OneYear then daishou else 0 end OneYearToTwoYear,
			Case When AuditDate<=@TwoYear then daishou else 0 end TwoYearAbove,daishou Total
			from (Select CompanyId,YingShouPrice-ShiShouPrice daishou,CONVERT(varchar(10),AuditDate,120) AuditDate from [Order] 
					Where YingShouPrice-ShiShouPrice>0 And StateId>=1 And NOE_Flag<>2 And FinanceRenlingDate is not null) x) y 
					join Zzjg z1 on y.CompanyId=z1.CompanyId join Zzjg z2 on z1.pid=z2.id
	 where z2.id in (281,282)
	union
	Select 5,0,'总计',Sum(OneMonth) OneMonth,Sum(OneToThreeMonth) OneToThreeMonth,Sum(ThreeToSixMonth) ThreeToSixMonth,
		Sum(SixMonthToOneYear) SixMonthToOneYear,Sum(OneYearToTwoYear) OneYearToTwoYear,Sum(TwoYearAbove) TwoYearAbove,Sum(Total) Total
	 from (Select x.CompanyId,
			Case When AuditDate>@OneMonth and AuditDate<=@CurrentDate then daishou else 0 end OneMonth,
			Case When AuditDate>@ThreeMonth and AuditDate<=@OneMonth then daishou else 0 end OneToThreeMonth,
			Case When AuditDate>@SixMonth and AuditDate<=@ThreeMonth then daishou else 0 end ThreeToSixMonth,
			Case When AuditDate>@OneYear and AuditDate<=@SixMonth then daishou else 0 end SixMonthToOneYear,
			Case When AuditDate>@TwoYear and AuditDate<=@OneYear then daishou else 0 end OneYearToTwoYear,
			Case When AuditDate<=@TwoYear then daishou else 0 end TwoYearAbove,daishou Total
			from (Select CompanyId,YingShouPrice-ShiShouPrice daishou,CONVERT(varchar(10),AuditDate,120) AuditDate from [Order] 
					Where YingShouPrice-ShiShouPrice>0 And StateId>=1 And NOE_Flag<>2 And FinanceRenlingDate is not null) x) y 
					join Zzjg z1 on y.CompanyId=z1.CompanyId join Zzjg z2 on z1.pid=z2.id
	 where z2.id in (12,13,14,281,282)
END
